<?php
require_once 'config.php';
class MakeSearchData
{
    private $db;
    public function __construct ()
    {
        $this->db = mysql_connect(DB_HOST, DB_USER, DB_PWD) or die('Could not connect: ' . mysql_error());
      //  echo DB_PREFIX . '_' . DB_NAME;
        mysql_select_db(DB_PREFIX . '_' . DB_NAME) or die('Could not select database');
        mysql_query('SET NAMES utf8');
      //  echo 'Connected successfully';
      //  echo "\n";
      //  echo "Please waiting ...\n";

    }
    
    public function createTable(){
        //echo 'start';
    	$sql = "DROP TABLE IF EXISTS `global_search_data`;";
    	mysql_query($sql);
        $sql = "    CREATE TABLE `global_search_data` (
              `id` int(11) NOT NULL auto_increment PRIMARY KEY ,
              `category_id_1` int(11) default NULL,
              `category_id_2` int(11) default NULL,
              `product_id` int(11) default NULL,
              `product_item_id` int(11) default NULL,
              `brand` varchar(100) default NULL,
              `key_words` text default NULL,              
              `created` datetime default NULL,
              `modified` datetime default NULL,
              `is_delete` int(11) default '0'
           )  ENGINE = MYISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
    	";
    	$result = mysql_query($sql);
    	return $result;
    	
    }
    
    private function generateData($products){
    	$data = array();
    	while($result = mysql_fetch_array($products,MYSQL_ASSOC)){
    		$data['piid'] = $result['piid'];
    		$data['pid'] = $result['pid'];
    		$data['key_words'] = trim($result['title']);
    		
    	//获得Category Info
    		$sql = "select cp.category_id, c.parent_id, c.short_name  from category_products cp 
    		     join categories c on cp.category_id = c.id and c.is_delete = 0 where cp.product_id = {$result['pid']} and cp.is_delete = 0;
    		  ";
    		$categoryInfo = mysql_query($sql);
    		if($categoryInfo){
    			$arr_sort = array();
                $int_parentid = 0;
    		    while($category = mysql_fetch_array($categoryInfo,MYSQL_ASSOC)){
    		    	$data['key_words'] .= " ".trim($category['short_name']);
    		    	if($int_parentid == $category['parent_id']){
    		    		$arr_sort[] = $category;
        	            $int_parentid = $category['category_id'];
    		    	} else {
    		    		break;
    		    	}
    		    }
    		}
    		if($arr_sort){
    			$data['category_id_1'] = $arr_sort[0]['category_id'];
    			$data['category_id_2'] = $arr_sort[count($arr_sort)-1]['category_id'];
    		}
    		
    		
    		
    		
    		//查找品牌
    		$sql = "select im.product_item_id, m.title as attr, mo.title as val from item_metadatas im 
    		    join metadatas m on im.metadata_id = m.id and m.title like '%品牌%' and m.is_delete=0
    		    join metadata_options mo on  im.value = mo.id and mo.is_delete=0
    		    where im.product_item_id = {$result['piid']}  and im.is_delete=0 ;
    		";
    		$brandInfo = mysql_query($sql);
    		if($brandInfo){
    		   while($brand = mysql_fetch_array($brandInfo,MYSQL_ASSOC)){
    		   	  $data['brand'] = trim($brand['val']);
    		   	  break;
    		   }
    		}
    		
    		//获得所有Metadata
    		$sql = "select im.product_item_id, im.metadata_id,im.value,im.desc_value,m.title,m.input_type from item_metadatas im 
    		    join metadatas m on m.id = im.metadata_id and m.in_advanced_search =1 and m.is_delete = 0
    		    where im.product_item_id = {$result['piid']}  and im.is_delete=0 ;
    		";
    		$metadata = mysql_query($sql);
    		if($metadata){
    			$pBrand = "";
    			$pModel = "";
    			$chBrand = "";
    			$enBrand = "";
    			while($value = mysql_fetch_array($metadata,MYSQL_ASSOC)){
 // 优化品牌和型号   	
                    
                    $mValue = $this->getMetadataVal($value);
                   		
    			    if($value['title'] == "品牌" || $value['title'] == "商品品牌"){
    			    		
    			        if(strpos($mValue,'（') && strpos($mValue,'）')){
    			        	preg_match('/^(.+)（(.*)）$/',$mValue,$match);
    			        	$chBrand = trim($match[1]);
    			        	$enBrand = trim($match[2]);
    			        	$pBrand = $chBrand." ".$enBrand;
    			        	
    			        } 
    			    } else if($value['title'] == "型号" || $value['title'] == "商品型号"){
    				       $pModel = $mValue;
    			    } 	
    				    
    			    $data['key_words'] .= " ".$mValue;
    			    
    			}
    			
    			if($pBrand){
    				$data['key_words'] .= " ".$pBrand;
    			}
    			if($pModel){
    				if($pBrand){
    				   if($enBrand){
    				   	   $pModelNew = $chBrand.$pModel.$enBrand.$pModel.$chBrand;
    				   	 
    				   } else {
    				   	   $pModelNew = $chBrand.$pMode.$chBrand;
    				   }
    				   $data['key_words'] .= " ".$pModelNew;
    				  
    				} else {
    					$data['key_words'] .= " ".$pModel;
    				}
    			}
    		}
    		 
    		
    		
    	// 向 global_search_data表写入数据	
    		$sql = "insert into global_search_data(key_words,category_id_1,category_id_2,product_id,product_item_id,brand,created,modified) 
    	 	    values('{$data['key_words']}',{$data['category_id_1']},{$data['category_id_2']},{$data['pid']},{$data['piid']},'{$data['brand']}',now(),now());
    	 	";
    		mysql_query($sql);
    	}
    }
    
    public function createData(){
    	$this->createTable();
    	$sql = "select p.id as pid, p.title,p.description,pi.id as piid from products p 
    	    join product_items pi on pi.product_id = p.id and pi.default_item = 1  and pi.is_delete=0 where p.is_delete=0;";
    	$products = mysql_query($sql);
    	
    	$this->generateData($products);	
    	//	print_r($data);
    	//	print_r("<br/>");
    	    //echo '.';
    	
    	 //添加全文索引
    	$this->addFulltext();	
    
    	//echo 'finish';
    }
    
    
    private function addFulltext(){
    	$sql = "ALTER IGNORE TABLE global_search_data ADD FULLTEXT(key_words) WITH PARSER mysqlcft;";
    	mysql_query($sql);
    }
    
    private function getMetadataVal($metadata){
          if($metadata['input_type']==1 || $metadata['input_type']==5 ){
    			$value = $metadata['value'];
    	  }
    	  if($metadata['input_type']==2){
    			$value = $metadata['desc_value'];
    	  }
    	  if($metadata['input_type']==3 || $metadata['input_type']==4 || $metadata['input_type']==6){
    			$sql = "select mo.title from metadata_options mo where mo.id = {$metadata['value']} and is_delete = 0;";
    	        $mTitle = mysql_query($sql);
    			if($mTitle){
    				$value = "";
    				while($mResult = mysql_fetch_array($mTitle,MYSQL_ASSOC)){
    					$value .= trim($mResult['title'])." ";
    				}
    			}
    	  }
    	  
    	  return trim($value);
    }
    
    public function createDataByCategory($categoryIds){
    	$sql = "select product_id from category_products where category_id in({$categoryIds}) and is_delete = 0; ";
    	$productArr = mysql_query($sql);
    	
    	if($productArr){
    		$productIds = '';
    		while($product = mysql_fetch_array($productArr,MYSQL_ASSOC)){
    			if($product['product_id']){
    			    $productIds .= $product['product_id'].',';
    			}
    		}
    		$productIds = substr($productIds,0,strrpos($productIds,','));
	   
    //删除对应product_id的数据		
    		$sql = "delete from global_search_data where product_id in ({$productIds});";
    		mysql_query($sql);
    //查找新的产品数据		
    		$sql = "select p.id as pid, p.title,p.description,pi.id as piid from products p 
    	    join product_items pi on pi.product_id = p.id and pi.default_item = 1  and pi.is_delete=0 where p.id in ({$productIds}) and p.is_delete=0;";
    	    $products = mysql_query($sql);
    	    $this->generateData($products);	 
    	}
    	
        
    }
    
    public function close(){
    	mysql_close($this->db);
    }
}
$a = new MakeSearchData();
try {
  if(SEARCH_CATEGORY_ID){
  	$a->createDataByCategory(SEARCH_CATEGORY_ID);
  } else {
     $a->createData();
  }
  $a->close();
} catch(Exception $e){
	//echo 'connection error!';
	$a->close();
} 